package com.xcw.bb.daoImpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.xcw.bb.dao.InteractDao;
import com.xcw.bb.entity.Accusation;
import com.xcw.bb.entity.Course;
import com.xcw.bb.entity.Interact;
import com.xcw.bb.entity.User;
import com.xcw.bb.util.JdbcUtil;

public class InteractDaoImpl implements InteractDao {

	@Override
	public List<Interact> findAllstudentInteract(int id) throws Exception {
		// TODO Auto-generated method stub
		Connection conn=null;
		PreparedStatement pstm=null;
		ResultSet rs=null;
		
		try {
			conn=JdbcUtil.getConnection();
			String sql="select * from t_interact where sid=?";
			pstm=conn.prepareStatement(sql);
			pstm.setInt(1, id);
			rs=pstm.executeQuery();
			List<Interact>list=new ArrayList<Interact>();
			
			while(rs.next()){
				Interact interact=new Interact();
				interact.setInteractId(rs.getInt(1));
				interact.setInteractName(rs.getString(2));
				interact.setInteractDetail(rs.getString(3));
				interact.setDepartment(rs.getString(4));
				interact.setStudentName(rs.getString(5));
				interact.setTeacherName(rs.getString(6));
				interact.setFlagAnswer(rs.getString(7));
				interact.setAnswerContent(rs.getString(8));
				interact.setFlagEstimate(rs.getString(9));
				interact.setEstimateContent(rs.getString(10));
				interact.setTuId(rs.getInt(11));
				interact.setSid(rs.getInt(12));
				interact.setScore(rs.getDouble(13));
				interact.setCreateDate(new java.util.Date(rs.getDate(14).getTime()));
				interact.setAnswerTime(new java.util.Date(rs.getDate(15).getTime()));
				interact.setEstimateTime(new java.util.Date(rs.getDate(16).getTime()));
				interact.setTeacherDepartment(rs.getString(17));
				interact.setFlagTeacherAccusation(rs.getString(18));
				interact.setFlagStudentAccusation(rs.getString(19));
				
				list.add(interact);
			}
			return list;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		}finally{
			JdbcUtil.close(rs, pstm, null);
		}
	}

	@Override
	public List<Interact> findLikeStudentInteractOfTeacherDepartment(
			String answer, String teacher_department, int id) throws Exception {
		// TODO Auto-generated method stub
		Connection conn=null;
		PreparedStatement pstm=null;
		ResultSet rs=null;
		
		try {
			conn=JdbcUtil.getConnection();
			String sql="select * from t_interact where flag_answer=? and teacher_department like ? and sid=?";
			pstm=conn.prepareStatement(sql);
			pstm.setString(1,answer);
			String str="%"+teacher_department+"%";
			pstm.setString(2,str);
			pstm.setInt(3, id);
			rs=pstm.executeQuery();
			List<Interact>list=new ArrayList<Interact>();
			
			while(rs.next()){
				Interact interact=new Interact();
				interact.setInteractId(rs.getInt(1));
				interact.setInteractName(rs.getString(2));
				interact.setInteractDetail(rs.getString(3));
				interact.setDepartment(rs.getString(4));
				interact.setStudentName(rs.getString(5));
				interact.setTeacherName(rs.getString(6));
				interact.setFlagAnswer(rs.getString(7));
				interact.setAnswerContent(rs.getString(8));
				interact.setFlagEstimate(rs.getString(9));
				interact.setEstimateContent(rs.getString(10));
				interact.setTuId(rs.getInt(11));
				interact.setSid(rs.getInt(12));
				interact.setScore(rs.getDouble(13));
				interact.setCreateDate(new java.util.Date(rs.getDate(14).getTime()));
				interact.setAnswerTime(new java.util.Date(rs.getDate(15).getTime()));
				interact.setEstimateTime(new java.util.Date(rs.getDate(16).getTime()));
				interact.setTeacherDepartment(rs.getString(17));
				interact.setFlagTeacherAccusation(rs.getString(18));
				interact.setFlagStudentAccusation(rs.getString(19));
				
				list.add(interact);
			}
			return list;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		}finally{
			JdbcUtil.close(rs, pstm, null);
		}
	}

	@Override
	public List<Interact> findLikeStudentInteractOfTeacherName(String answer,
			String teacher_name, int id) throws Exception {
		// TODO Auto-generated method stub
		Connection conn=null;
		PreparedStatement pstm=null;
		ResultSet rs=null;
		
		try {
			conn=JdbcUtil.getConnection();
			String sql="select * from t_interact where flag_answer=? and teacher_name like ? and sid=?";
			pstm=conn.prepareStatement(sql);
			pstm.setString(1,answer);
			String str="%"+teacher_name+"%";
			pstm.setString(2,str);
			pstm.setInt(3, id);
			rs=pstm.executeQuery();
			List<Interact>list=new ArrayList<Interact>();
			
			while(rs.next()){
				Interact interact=new Interact();
				interact.setInteractId(rs.getInt(1));
				interact.setInteractName(rs.getString(2));
				interact.setInteractDetail(rs.getString(3));
				interact.setDepartment(rs.getString(4));
				interact.setStudentName(rs.getString(5));
				interact.setTeacherName(rs.getString(6));
				interact.setFlagAnswer(rs.getString(7));
				interact.setAnswerContent(rs.getString(8));
				interact.setFlagEstimate(rs.getString(9));
				interact.setEstimateContent(rs.getString(10));
				interact.setTuId(rs.getInt(11));
				interact.setSid(rs.getInt(12));
				interact.setScore(rs.getDouble(13));
				if(rs.getDate(14)==null){interact.setCreateDate(null);}
				else{interact.setCreateDate(new java.util.Date(rs.getDate(14).getTime()));}
				if(rs.getDate(15)==null){interact.setCreateDate(null);}
				else{interact.setAnswerTime(new java.util.Date(rs.getDate(15).getTime()));}
				if(rs.getDate(16)==null){interact.setCreateDate(null);}
				else{interact.setEstimateTime(new java.util.Date(rs.getDate(16).getTime()));}
				interact.setTeacherDepartment(rs.getString(17));
				interact.setFlagTeacherAccusation(rs.getString(18));
				interact.setFlagStudentAccusation(rs.getString(19));
				
				list.add(interact);
			}
			return list;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		}finally{
			JdbcUtil.close(rs, pstm, null);
		}
	}

	@Override
	public List<User> findAllTeacher() throws Exception {
		// TODO Auto-generated method stub
		Connection conn=null;
		PreparedStatement pstm=null;
		ResultSet rs=null;
		
		try {
			conn=JdbcUtil.getConnection();
			String sql="select * from t_user where role='teacher'";
			pstm=conn.prepareStatement(sql);
			rs=pstm.executeQuery();
			List<User>list=new ArrayList<User>();
			User user=null;
			while(rs.next()){
				user=new User();
				user.setUserId(rs.getInt(1));
				user.setUsername(rs.getString(2));
				user.setPassword(rs.getString(3));
				user.setRealname(rs.getString(4));
				user.setDepartment(rs.getString(5));
				user.setRole(rs.getString(6));
				
				list.add(user);
			}
			
			return list;
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		}finally{
			JdbcUtil.close(rs, pstm, null);
		}
	}

	@Override
	public List<Course> findCourseByTeacherId(int id) throws Exception {
		// TODO Auto-generated method stub
		Connection conn=null;
		PreparedStatement pstm=null;
		ResultSet rs=null;
		
		try {
			conn=JdbcUtil.getConnection();
			String sql="select * from t_course tc left join course_user cu on tc.course_id=cu.c_id where cu.u_id=?";
			pstm=conn.prepareStatement(sql);
			pstm.setInt(1, id);
			rs=pstm.executeQuery();
			List<Course>list=new ArrayList<Course>();
			Course course=null;
			while(rs.next()){
				course=new Course();
				course.setCourseId(rs.getInt(1));
				course.setCourseName(rs.getString(2));
				
				list.add(course);
			}
			
			return list;
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		}finally{
			JdbcUtil.close(rs, pstm, null);
		}
	}
	/*
	public void insertInteract(Interact interact){
		Connection conn = null ;
		PreparedStatement pstm = null;
		ResultSet rs = null ;
		try{
			conn = JdbcUtil.getConnection();
			String sql = "insert into t_interact values(interact_seq.nextval,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			pstm = conn.prepareStatement(sql);
			pstm.setString(1,interact.getInteractName());
			pstm.setString(2,interact.getInteractDetail());
			pstm.setString(3,interact.getDepartment());
			pstm.setString(4,interact.getStudentName());
			pstm.setString(5,interact.getTeacherName());
			pstm.setString(6,interact.getFlagAnswer());
			pstm.setString(7, interact.getAnswerContent());
			pstm.setString(8,interact.getFlagEstimate());
			pstm.setString(9,interact.getEstimateContent());
			if(interact.getTuId()==null)pstm.setInt(10,0);
			else pstm.setInt(10,interact.getTuId());
			if(interact.getSid()==null)pstm.setInt(11,0);
			else pstm.setInt(11,interact.getSid());
			pstm.setDouble(12,interact.getScore());
			if(interact.getCreateDate()==null)pstm.setDate(13,null);
			else pstm.setDate(13,new java.sql.Date(interact.getCreateDate().getTime()));
			if(interact.getAnswerTime()==null)pstm.setDate(14,null);
			else pstm.setDate(14,new java.sql.Date(interact.getAnswerTime().getTime()));
			if(interact.getEstimateTime()==null) pstm.setDate(15,null);
			else pstm.setDate(15,new java.sql.Date(interact.getEstimateTime().getTime()));
			pstm.setString(16,interact.getTeacherDepartment());
			pstm.setString(17,interact.getFlagTeacherAccusation());
			pstm.setString(18,interact.getFlagStudentAccusation());
			pstm.executeUpdate();

			
		}catch(Exception e){
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		}finally{
			JdbcUtil.close(null, pstm,null);
		}
	}*/

	@Override
	public void deleteOneInteract(int id) {
		// TODO Auto-generated method stub
		Connection conn=null;
		PreparedStatement pstm=null;
		try {
			conn=JdbcUtil.getConnection();
			String sql="delete from t_interact where interact_id=?";
			pstm=conn.prepareStatement(sql);
			pstm.setInt(1, id);
			pstm.executeUpdate();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		}finally{
			JdbcUtil.close(null, pstm, null);
		}
	}

	@Override
	public Interact findInteractDetail(int id) throws Exception {
		// TODO Auto-generated method stub
		Connection conn=null;
		PreparedStatement pstm=null;
		ResultSet rs=null;
		
		try {
			conn=JdbcUtil.getConnection();
			String sql="select * from t_interact ti left join t_accusation ta on ti.interact_id=ta.i_id where ti.interact_id=? ";
			pstm=conn.prepareStatement(sql);
			pstm.setInt(1, id);
            rs=pstm.executeQuery();
            Interact interact=new Interact();
            Accusation accusation=new Accusation();
            
            while(rs.next()){
            	interact.setInteractId(rs.getInt(1));
				interact.setInteractName(rs.getString(2));
				interact.setInteractDetail(rs.getString(3));
				interact.setDepartment(rs.getString(4));
				interact.setStudentName(rs.getString(5));
				interact.setTeacherName(rs.getString(6));
				interact.setFlagAnswer(rs.getString(7));
				interact.setAnswerContent(rs.getString(8));
				interact.setFlagEstimate(rs.getString(9));
				interact.setEstimateContent(rs.getString(10));
				interact.setTuId(rs.getInt(11));
				interact.setSid(rs.getInt(12));
				interact.setScore(rs.getDouble(13));
				if(rs.getDate(14)==null){
					interact.setCreateDate(null);
				}else{
					interact.setCreateDate(new java.util.Date(rs.getDate(14).getTime()));
				}
				if(rs.getDate(15)==null){
					interact.setAnswerTime(null);
				}else{
					interact.setAnswerTime(new java.util.Date(rs.getDate(15).getTime()));
				}
				if(rs.getDate(16)==null){
					interact.setEstimateTime(null);
				}else{
					interact.setEstimateTime(new java.util.Date(rs.getDate(16).getTime()));
				}
				interact.setTeacherDepartment(rs.getString(17));
				interact.setFlagTeacherAccusation(rs.getString(18));
				interact.setFlagStudentAccusation(rs.getString(19));
				
				accusation.setAccusationId(rs.getInt(20));
				accusation.setTeacherAccusationContent(rs.getString(21));
				if(rs.getDate(22)==null){	
					accusation.setStudentAccusationTime(null);
				}else{
					accusation.setStudentAccusationTime(new java.util.Date(rs.getDate(22).getTime()));
				}
				if(rs.getDate(23)==null){
						accusation.setTeacherAccusationTime(null);
				}else{
					accusation.setTeacherAccusationTime(new java.util.Date(rs.getDate(23).getTime()));
				}
				accusation.setIid(id);
				accusation.setStudentAccusationContent(rs.getString(25));
				
				interact.setAccusation(accusation);
            }
            return interact;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		}finally{
			JdbcUtil.close(rs, pstm, null);
		}
		
	}

	@Override
	public void updateByEvaluate(int id, String content) throws Exception {
		// TODO Auto-generated method stub
		Connection conn=null;
		PreparedStatement pstm=null;
		try {
			conn=JdbcUtil.getConnection();
			String sql="update t_interact set flag_estimate='y',estimate_content=? where interact_id=?";
			pstm=conn.prepareStatement(sql);
			pstm.setString(1, content);
			pstm.setInt(2, id);
			pstm.executeUpdate();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		}finally{
			JdbcUtil.close(null, pstm, null);
		}
	}

	@Override
	public void updateOneInteractAccusationFlag(int id) throws Exception {
		// TODO Auto-generated method stub
		Connection conn=null;
		PreparedStatement pstm=null;
		try{
			conn=JdbcUtil.getConnection();
			String sql="update t_interact set flag_student_accusation='y' where interact_id=?";
			pstm=conn.prepareStatement(sql);
			pstm.setInt(1, id);
			pstm.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		}finally{
			JdbcUtil.close(null, pstm, null);
		}
	}

	@Override
	public void updateOneInteractContent(int id, String content)
			throws Exception {
		// TODO Auto-generated method stub
		Connection conn=null;
		PreparedStatement pstm=null;
		try {
			conn=JdbcUtil.getConnection();
			String sql="update t_interact set  interact_detail=?   where interact_id=?";
			pstm=conn.prepareStatement(sql);
			pstm.setString(1, content);
			pstm.setInt(2, id);
			pstm.executeUpdate();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		}finally{
			JdbcUtil.close(null, pstm, null);
		}
	}

	@Override
	public void insertInteract(Interact interact) {
		// TODO Auto-generated method stub
		Connection conn=null;
		PreparedStatement pstm=null;
		ResultSet rs=null;
		try{
			conn=JdbcUtil.getConnection();
			String sql="insert into t_interact values(interact_seq.nextval,?,?,?,?,?,'N',null,null,null,?,?,null,?,null,null,?,'N','N')";				
			 
			pstm=conn.prepareStatement(sql);
			 
			pstm.setString(1, interact.getInteractName());
			pstm.setString(2,  interact.getInteractDetail() );
			pstm.setString(3,  interact.getDepartment() );
			pstm.setString(4,  interact.getStudentName() );
			pstm.setString(5,  interact.getTeacherName() );
			pstm.setInt(6, interact.getTuId()  );
			pstm.setInt(7,  interact.getSid() );
			 
			if(interact.getCreateDate()==null){
				pstm.setDate(8, null);
			}else{
				pstm.setTimestamp(8, new java.sql.Timestamp(interact.getCreateDate().getTime()) );
			}
			pstm.setString(9,  interact.getTeacherDepartment() );
			pstm.executeUpdate();
			
			/*rs=pstm.getGeneratedKeys();
			Integer id=null;
			if(rs.next()){		id=rs.getInt(1) ;	}
			return id;*/
		}catch(Exception e){
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		}finally{
			JdbcUtil.close(rs, pstm, null);
		}
	}

}
